![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Using ConstraintsConstraints can be implimented at one of the following times:
Implementing Constraints at Table Creation When creating tables, you can add constraints to the column definitions as show in the following CREATE TABLE command: CREATE TABLE breeds ( breed NUMBER(4) PRIMARY KEY, breed_name VARCHAR2(25) NOT NULL, description VARCHAR2(40) NOT NULL) TABLESPACE dog_space; CREATE TABLE dogs ( dogname VARCHAR2(40) NOT NULL, age NUMBER(2) NOT NULL CONSTRAINT age_check CHECK ( age < 20 ), breed NUMBER(4) CONSTRAINT breed_cons REFERENCES breeds ON DELETE CASCADE, owner VARCHAR2(40) NOT NULL ) TABLESPACE dog_space; When specifying referential integrity constraints, you can specify two additional options:
Implementing Constraints on Existing Tables For existing tables, you can add constraints with the ALTER TABLE command, as shown here (all three commands are required to alter the verification methods used by the DOGS and BREEDS tables introduced earlier in this chapter): ALTER TABLE breeds ADD PRIMARY KEY (breed); ALTER TABLE dogs ADD FOREIGN KEY (breed) REFERENCES breeds(breed); ALTER TABLE dogs ADD CHECK (age < 20); Viewing Constraints When you add constraints to tables, these definitions are added to the data dictionary. The constraint information is kept internal tables in the data dictionary. These tables can be queried in several views. The following chart lists the views defined for constraint information.
You can obtain information about which tables have constraints applied to them by querying the data dictionary in this manner: SQL> SELECT 2 constraint_name, 3 constraint_type, 4 table_name, 5 r_constraint_name 6 FROM 7 user_constraints; CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME -------------------- - ---------------- -------------- SYS_C00388 C BREEDS SYS_C00389 C BREEDS SYS_C00390 C BREEDS SYS_C00391 P BREEDS SYS_C00378 C DOGS SYS_C00379 C DOGS SYS_C00380 C DOGS SYS_C00381 C DOGS SYS_C00392 R DOGS SYS_C00391 SYS_C00404 C DOGS 10 rows selected. Alternatively, you can obtain specific constraint information in this way: SQL> SELECT 2 constraint_name, 3 search_condition 4 FROM 5 user_constraints 6 WHERE 7 table_name = 'DOGS'; CONSTRAINT_NAME ------------------------------ SEARCH_CONDITION -------------------------------------------------------------------------- SYS_C00378 DOGNAME IS NOT NULL SYS_C00379 AGE IS NOT NULL SYS_C00380 BREED IS NOT NULL SYS_C00381 OWNER IS NOT NULL SYS_C00392 SYS_C00404 age < 20 6 rows selected. To get column information, you can use the following statement: SQL> SELECT 2 constraint_name, 3 table_name, 4 column_name 5 FROM 6 user_cons_columns; CONSTRAINT_NAME TABLE_NAME COLUMN_NAME -------------------- -------------------- ---------- SYS_C00378 DOGS DOGNAME SYS_C00379 DOGS AGE SYS_C00380 DOGS BREED SYS_C00381 DOGS OWNER SYS_C00388 BREEDS BREED SYS_C00389 BREEDS BREED_NAME SYS_C00390 BREEDS DESCRIPTION SYS_C00391 BREEDS BREED SYS_C00392 DOGS BREED SYS_C00404 DOGS AGE 10 rows selected.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |